﻿using Microsoft.Win32;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace FormulaRemover
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        string obFileName = string.Empty;



        /// <summary>
        /// 在文件后缀前插入指定字符串
        /// </summary>
        /// <param name="fileName">完整文件名（如：document.xlsx）</param>
        /// <param name="insertString">要插入的字符串</param>
        /// <returns>插入后的新文件名</returns>
        public static string InsertBeforeExtension(string fileName, string insertString)
        {
            // 获取文件扩展名（包括点号）
            string extension = System.IO.Path.GetExtension(fileName);

            string newFileName = string.Empty;

            int lastSlashIndex = fileName.LastIndexOf(extension);

            if (lastSlashIndex != -1)
            {
                newFileName = fileName.Substring(0, lastSlashIndex) + insertString + extension;

            }

            return newFileName;
        }

        private void Button1_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();


            // 设置文件过滤器，限定为 .xlsx 格式
            openFileDialog.Filter = "Excel 工作簿 (*.xlsx)|*.xlsx";

            // 可选：设置其他常用属性
            openFileDialog.Title = "请选择要修改的Excel文件"; // 对话框标题

            openFileDialog.InitialDirectory = @"C:\";          // 初始目录

            openFileDialog.RestoreDirectory = true;           // 记住上次目录
            
            var result = openFileDialog.ShowDialog();

            if (result == true)
            {

                textbox1.Text = openFileDialog.FileName;

                obFileName = InsertBeforeExtension(openFileDialog.FileName, "rf");
            }
            else
            {
                MessageBox.Show("没有选定有效的文件，点击确定后重试。","注意！");
            }
        }

        private void Button2_Click(object sender, RoutedEventArgs e)
        {
            ExcelPackage.License.SetNonCommercialPersonal("zhwang");

            string fileName = textbox1.Text;

            using (var package = new ExcelPackage(new FileInfo(fileName)))
            {

                if (radioButton1.IsChecked==true)
                {
                    foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
                    {

                        // 遍历工作表的所有单元格
                        foreach (var cell in worksheet.Cells)
                        {
                            // 检查单元格是否包含公式
                            if (cell.Formula != null && cell.Formula != "")
                            {
                                // 获取公式计算后的值
                                var cellValue = cell.Value;

                                // 清除公式但保留值
                                cell.Formula = null;

                                cell.Value = cellValue;
                            }
                        }


                    }
                    
                }
                else
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                    // 遍历工作表的所有单元格
                    foreach (var cell in worksheet.Cells)
                    {
                        // 检查单元格是否包含公式
                        if (cell.Formula != null && cell.Formula != "")
                        {
                            // 获取公式计算后的值
                            var cellValue = cell.Value;

                            // 清除公式但保留值
                            cell.Formula = null;

                            cell.Value = cellValue;
                        }
                    }
                }
                
                // 保存文件
                package.SaveAs(new FileInfo(obFileName));

                MessageBox.Show("everything is ok!");
            }
        }
    }
}
